﻿create procedure [dbo].[usp_ExceptionInsert]
	@PublicationId uniqueidentifier
	, @AppDomainId int
	, @AssemblyId int
	, @MachineId int
	, @IIdentityId int
	, @ProcessId int
	, @TargetSiteId int
	, @ThreadId int
	, @WorkItemId int
	, @ExceptionType nvarchar(255)
	, @HelpLink nvarchar(512)
	, @HResult int
	, @IsTransient bit
	, @Message nvarchar(512)
	, @Source nvarchar(512)
	, @StackTrace nvarchar(max)
	, @Unhandled bit
	, @FirstChance bit
	, @SerializedDictionaryXml xml
	, @SerializedExceptionXml xml
	, @XElement xml
as

begin try
	
	begin transaction
	
		if not exists(select * from [dbo].[Exception] where [PublicationId] = @PublicationId and
														    ([AppDomainId] = @AppDomainId or ([AppDomainId] is null and @AppDomainId is null)) and
															[AssemblyId] = @AssemblyId and
															([MachineId] = @MachineId or ([MachineId] is null and @MachineId is null)) and
															[IIdentityId] = @IIdentityId and
															([ProcessId] = @ProcessId or ([ProcessId] is null and @ProcessId is null)) and
															[TargetSiteId] = @TargetSiteId and
															[ThreadId] = @ThreadId and
															([WorkItemId] = @WorkItemId or ([WorkItemId] is null and @WorkItemId is null)) and
															[ExceptionType] = @ExceptionType and
															([HelpLink] = @HelpLink or ([HelpLink] is null and @HelpLink is null)) and
															([HResult] = @HResult or ([HResult] is null and @HResult is null)) and
															([IsTransient] = @IsTransient or ([IsTransient] is null and @IsTransient is null)) and
															[Message] = @Message and
															([Source] = @Source or ([Source] is null and @Source is null)) and
															([StackTrace] = @StackTrace or ([StackTrace] is null and @StackTrace is null)) and
															[dbo].[CompareXml]([SerializedDictionaryXml], @SerializedDictionaryXml) = 0 and
															[dbo].[CompareXml]([SerializedExceptionXml], @SerializedExceptionXml) = 0 and
															([Unhandled] = @Unhandled or ([Unhandled] is null and @Unhandled is null)) and
															([FirstChance] = @FirstChance or ([FirstChance] is null and @FirstChance is null)) and
															[dbo].[CompareXml]([XElement], @XElement) = 0)
		
			begin
										                              
				insert [dbo].[Exception]
				(
					[PublicationId]
					, [AppDomainId]
					, [AssemblyId]
					, [MachineId]
					, [IIdentityId]
					, [ProcessId]
					, [TargetSiteId]
					, [ThreadId]
					, [WorkItemId]
					, [ExceptionType]
					, [HelpLink]
					, [HResult]
					, [IsTransient]
					, [Message]
					, [Source]
					, [StackTrace]
					, [Unhandled]
					, [FirstChance]
					, [SerializedDictionaryXml]
					, [SerializedExceptionXml]
					, [XElement]
				)
				values
				(
					@PublicationId
					, @AppDomainId
					, @AssemblyId
					, @MachineId
					, @IIdentityId
					, @ProcessId
					, @TargetSiteId
					, @ThreadId
					, @WorkItemId
					, @ExceptionType
					, @HelpLink
					, @HResult
					, @IsTransient
					, @Message
					, @Source
					, @StackTrace
					, @Unhandled
					, @FirstChance
					, @SerializedDictionaryXml
					, @SerializedExceptionXml
					, @XElement
				)
				
				select [ExceptionId], [PublicationId], [ConcurrencyId], [Created], [Updated] from [dbo].[Exception] where @@rowcount > 0 and [ExceptionId] = scope_identity()
			
			end
			
		else
			
			begin
			
				select [ExceptionId], [PublicationId], [ConcurrencyId], [Created], [Updated] from [dbo].[Exception] where [PublicationId] = @PublicationId and
																												([AppDomainId] = @AppDomainId or ([AppDomainId] is null and @AppDomainId is null)) and
																												[AssemblyId] = @AssemblyId and
																												([MachineId] = @MachineId or ([MachineId] is null and @MachineId is null)) and
																												[IIdentityId] = @IIdentityId and
															                                                    ([ProcessId] = @ProcessId or ([ProcessId] is null and @ProcessId is null)) and
																												[TargetSiteId] = @TargetSiteId and
																												[ThreadId] = @ThreadId and
																												([WorkItemId] = @WorkItemId or ([WorkItemId] is null and @WorkItemId is null)) and
																												[ExceptionType] = @ExceptionType and
																												([HelpLink] = @HelpLink or ([HelpLink] is null and @HelpLink is null)) and
																												([HResult] = @HResult or ([HResult] is null and @HResult is null))  and
																												([IsTransient] = @IsTransient or ([IsTransient] is null and @IsTransient is null)) and
																												[Message] = @Message and
																												([Source] = @Source or ([Source] is null and @Source is null)) and
																												([StackTrace] = @StackTrace or ([StackTrace] is null and @StackTrace is null)) and
																												[dbo].[CompareXml]([SerializedDictionaryXml], @SerializedDictionaryXml) = 0 and
																												[dbo].[CompareXml]([SerializedExceptionXml], @SerializedExceptionXml) = 0 and
																												([Unhandled] = @Unhandled or ([Unhandled] is null and @Unhandled is null)) and
															                                                    ([FirstChance] = @FirstChance or ([FirstChance] is null and @FirstChance is null)) and
																												[dbo].[CompareXml]([XElement], @XElement) = 0
			end
	
	commit transaction

end try
	
begin catch
	if @@trancount > 0
	begin
		rollback transaction;
	end
	execute [dbo].[usp_SqlErrorInsert]
end catch


